package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.ProductDao;
import com.windfone.model.Product;

public class ProductDaoImpl implements ProductDao {
	
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String SQL_DELETE_PRODUCT=" DELETE FROM  product WHERE id=?";
	public int  deleteProduct(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_DELETE_PRODUCT,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String SQL_UPDATE_PRODUCT_STATUS=" UPDATE  product SET status=? WHERE id=?" ;
	public int updateProductStatus(final int id ,final int status ){
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_UPDATE_PRODUCT_STATUS,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,status);
						ps.setInt(i++,id);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String SQL_UPDATE_PRODUCT=" UPDATE  product SET " +
				" pointSeq=?,name=?, productTypeId=?, marketTime=?, mobileFormat=?, phoneShape=?," +
				" screenSize=?,screenParam=?,system=?, gps=?,camera=?," +
				" langId=?,price=?,cpu=?,memory=?,standard=?, "+
				" talkTime=?,shapeSize=?,shapeColor=?, weight=? ,waitTime=?," +
				" screenColor=? ,mobilePhase=? ,screenObject=?, type=?,imagepath=?,bigImagepath=?,service=?"+
				" WHERE id=?";

	public int updateProduct(final Product  product) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_UPDATE_PRODUCT,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,product.getPointSeq());
						ps.setString(i++,product.getName());
						ps.setInt(i++,product.getProductTypeId());
						ps.setString(i++,product.getMarketTime());
						ps.setString(i++,product.getMobileFormat());
						ps.setString(i++,product.getPhoneShape());
						
						ps.setString(i++,product.getScreenSize());
						ps.setString(i++,product.getScreenParam());
						ps.setString(i++,product.getSystem());
						ps.setString(i++,product.getGps());
						ps.setString(i++,product.getCamera());
						
						ps.setInt(i++, product.getLangId());
						ps.setDouble(i++, product.getPrice());
						ps.setString(i++,product.getCpu());
						ps.setString(i++,product.getMemory());
						ps.setString(i++,product.getStandard());
						
						ps.setString(i++,product.getTalkTime());
						ps.setString(i++,product.getShapeSize());
						ps.setString(i++,product.getShapeColor());
						ps.setString(i++,product.getWeight());
						ps.setString(i++,product.getWaitTime());
						
						ps.setString(i++,product.getScreenColor());
						ps.setString(i++,product.getMobilePhase());
						ps.setString(i++,product.getScreenObject());
						ps.setString(i++,product.getType());
						ps.setString(i++,product.getImagepath());
						ps.setString(i++,product.getBigImagepath());
						ps.setString(i++,product.getService());
						
						ps.setInt(i++,product.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_INSERT_PRODUCT_TYPE=" INSERT INTO product (" +
			" name, productTypeId, marketTime, mobileFormat, phoneShape, " +
			" screenSize, screenParam, system, gps, camera, " +
			 " langId,price,cpu,memory,standard," +
			" talkTime,shapeSize,shapeColor,weight,waitTime," + 
			" screenColor,mobilePhase,screenObject,type,imagepath,bigImagepath,service,pointSeq) VALUES( " +
			" ?,?,?,?,?, ?,?,?,?,?, " +
			 "?,?,?,?,?, ?,?,?,?,?, " + 
			"?,?,?,?,?,?,?,?)   ";

	public int insertProduct(final Product product) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_INSERT_PRODUCT_TYPE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,product.getName());
						ps.setInt(i++,product.getProductTypeId());
						ps.setString(i++,product.getMarketTime());
						ps.setString(i++,product.getMobileFormat());
						ps.setString(i++,product.getPhoneShape());
						
						ps.setString(i++,product.getScreenSize());
						ps.setString(i++,product.getScreenParam());
						ps.setString(i++, product.getSystem());
						ps.setString(i++,product.getGps());
						ps.setString(i++,product.getCamera());
						
						ps.setInt(i++, product.getLangId());
						ps.setDouble(i++, product.getPrice());
						ps.setString(i++,product.getCpu());
						ps.setString(i++,product.getMemory());
						ps.setString(i++,product.getStandard());
						
						ps.setString(i++,product.getTalkTime());
						ps.setString(i++,product.getShapeSize());
						ps.setString(i++,product.getShapeColor());
						ps.setString(i++,product.getWeight());						
						ps.setString(i++,product.getWaitTime());	
						
						ps.setString(i++,product.getScreenColor());		
						ps.setString(i++,product.getMobilePhase());
						ps.setString(i++,product.getScreenObject());
						ps.setString(i++,product.getType());
						ps.setString(i++,product.getImagepath());
						ps.setString(i++,product.getBigImagepath());
						ps.setString(i++,product.getService());
						ps.setInt(i++,product.getPointSeq());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_GET_PRODUCT_TYPE_BYID=" SELECT * FROM  product  WHERE id=?";
	public   List<Product>  getProductById(final int keyValue) {
		final List<Product> result = new ArrayList<Product>();
		try {
			jdbcTemplate.query(
				SQL_GET_PRODUCT_TYPE_BYID,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Product to= new Product();
							
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight"));
 							to.setWaitTime(rs.getString("waitTime"));
 							
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String SQL_GET_ALL_PRODUCT_TYPE=" SELECT p.*, t.typeName FROM  product p ,producttype t WHERE  p.productTypeId=t.id  ";
	public List<Product> getAllProduct( final int beginRecord,final String whereCondition) {
		final List<Product> result = new ArrayList<Product>();
		String sql = SQL_GET_ALL_PRODUCT_TYPE+ whereCondition + "  ORDER BY id LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Product to= new Product();
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));    
 							
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight")); 
 							to.setWaitTime(rs.getString("waitTime"));
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setProductytypeName(rs.getString("typeName"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	String SQL_GET_ACOUNT_PRODUCT=" SELECT COUNT(1) FROM  product WHERE 1=1 ";
	public int getAllRecorderNumber( final String whereCondition ) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_PRODUCT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	String SQL_GET_PRODUCTS_BY_COMMEND=" SELECT * FROM  product  WHERE id=?";
	public List<Product> getHotMachine(final int commendFlag) {
		final List<Product> result = new ArrayList<Product>();
		try {
			jdbcTemplate.query(
					SQL_GET_PRODUCTS_BY_COMMEND,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, commendFlag);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Product to= new Product();
							
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight"));
 							to.setWaitTime(rs.getString("waitTime"));
 							
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private String SQL_GET_PRODUCTS_FIRST_COMMEND_PAGE = "  SELECT * FROM product p,recommend r,recommendtype t WHERE p.id = r.recordId AND "+ 
		"t.id=r.recommendTypeId AND  t.name=? ORDER BY p.pointSeq DESC ";
	public List<Product> getCommendProductByCommendType(final String recommendTypeName) {
		final List<Product> result = new ArrayList<Product>();
		try {
			jdbcTemplate.query(
					SQL_GET_PRODUCTS_FIRST_COMMEND_PAGE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, recommendTypeName);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Product to= new Product();
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight"));
 							to.setWaitTime(rs.getString("waitTime"));
 							
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 
	String SQL_GET_ALL_PRODUCT_BY_STATUS=" SELECT p.* FROM product p,producttype t WHERE  p.productTypeId=t.id AND p.status=? AND p.productTypeId=?  ORDER BY  p.pointSeq DESC,p.id";
	public List<Product> getAllProductByStatus(final int typeId, final int status) {
		final List<Product> result = new ArrayList<Product>();
		try {
			 jdbcTemplate.query(
				SQL_GET_ALL_PRODUCT_BY_STATUS,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, status);
						ps.setInt(2, typeId);
					}
				},				
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Product to= new Product();
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));    
 							
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight")); 
 							to.setWaitTime(rs.getString("waitTime"));
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
// 							to.setProductytypeName(rs.getString("typeName"));
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String SQL_GET_ALL_PRODUCT_BY_WHERE=" SELECT p.* FROM product p,producttype t WHERE ";
	public List<Product> getAllProductByWherecondition(String whereStr) {
		final List<Product> result = new ArrayList<Product>();
		String sql = SQL_GET_ALL_PRODUCT_BY_WHERE+whereStr + "   ORDER BY  p.pointSeq DESC,p.id";
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Product to= new Product();
 							to.setId(rs.getInt("id"));
 							to.setName( rs.getString("name"));      
 							to.setProductTypeId( rs.getInt("productTypeId"));  
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setMobileFormat(rs.getString("mobileformat"));
 							to.setPhoneShape(rs.getString("phoneShape"));
 							to.setScreenSize(rs.getString("screenSize"));
 							to.setScreenParam(rs.getString("screenParam"));
 							to.setGps(rs.getString("gps"));       
 							to.setCamera(rs.getString("camera"));    
 							to.setLangId(rs.getInt("langId"));    
 							to.setPrice(rs.getDouble("price"));    
 							
 							to.setCpu(rs.getString("cpu"));
 							to.setMemory(rs.getString("memory"));
 							to.setStandard(rs.getString("standard"));
 							to.setTalkTime(rs.getString("talkTime"));
 							to.setShapeSize(rs.getString("shapeSize"));
 							to.setShapeColor(rs.getString("shapeColor"));
 							to.setWeight(rs.getString("weight")); 
 							to.setWaitTime(rs.getString("waitTime"));
 							to.setScreenColor(rs.getString("screenColor"));
 							to.setSystem(rs.getString("system"));
 							to.setMobilePhase(rs.getString("mobilePhase"));
 							to.setScreenObject(rs.getString("screenObject"));
 							to.setType(rs.getString("type"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigImagepath(rs.getString("bigImagepath"));
 							to.setService(rs.getString("service"));
 							to.setPointSeq(rs.getInt("pointSeq"));
 							to.setStatus(rs.getInt("status"));
// 							to.setProductytypeName(rs.getString("typeName"));
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	 
}
